﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

namespace U01_boke
{
    /// <summary>
    /// T-SQL管理类
    /// </summary>
    public class SQLBuilder
    {
        #region API管理
        /// <summary>
        /// 生成查询API单个数据T-SQL
        /// </summary>
        /// <param name="ip">ip地址</param>
        /// <returns></returns>
        public static (string sql, SqlParameter[] param) API_Single_SQL(string api_code)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" SELECT [Api_Code],[Api_Remark],[Api_Method],[Api_Http_Method],[Create_Time]");
            builder.Append(" FROM [sys_api]");
            builder.Append(" WHERE Api_Code = @api_code");
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@api_code",api_code)
            };
            return (builder.ToString(), parameters);
        }

        #endregion

        #region 留言管理
        /// <summary>
        /// 生成查询留言分页T-SQL
        /// </summary>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页数量</param>
        public static (string sql, SqlParameter[] param) Message_Paged_SQL(int pageIndex, int pageSize)
        {
            int pageEnd = pageIndex * pageSize;
            int pageBegin = pageEnd + 1 - pageSize;

            StringBuilder builder = new StringBuilder();
            builder.Append(" SELECT [ID],[Content],[CreateTime],[Name],[HeadImg],[IP],[Country],[Region],[City],[County],[Isp]");
            builder.Append(" FROM (");
            builder.Append(" SELECT ROW_NUMBER()OVER(ORDER BY m.ID DESC)rownumber,");
            builder.Append(" m.ID,m.Content,m.CreateTime,v.Name,v.HeadImg,i.IP,i.Country,i.Region,i.City,i.County,i.Isp FROM [u_Message] m");
            builder.Append(" INNER JOIN u_Visitor v ON m.VID = v.ID");
            builder.Append(" INNER JOIN sys_IP i ON v.IPID = i.ID) MM");
            builder.Append(" WHERE rownumber BETWEEN @pageBegin AND @pageEnd");
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@pageBegin",pageBegin),
                new SqlParameter("@pageEnd",pageEnd)
            };
            return (builder.ToString(), parameters);
        }

        /// <summary>
        /// 生成查询留言数据记录总数T-SQL
        /// </summary>
        public static (string sql, SqlParameter[] param) Message_All_Count_SQL()
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" SELECT COUNT(1) FROM u_Message m");
            builder.Append(" INNER JOIN u_Visitor v ON m.VID = v.ID");
            builder.Append(" INNER JOIN sys_IP i ON v.IPID = i.ID");
            return (builder.ToString(), null);
        }
        /// <summary>
        /// 生成查询所有留言T-SQL
        /// </summary>
        public static (string sql, SqlParameter[] param) Message_All_SQL()
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" SELECT m.ID,m.Content,m.CreateTime,v.Name,v.HeadImg,i.IP,i.Region,i.City,i.County,i.Isp");
            builder.Append(" FROM u_Message m");
            builder.Append(" INNER JOIN u_Visitor v ON m.VID = v.ID");
            builder.Append(" INNER JOIN sys_IP i ON v.IPID = i.ID");
            return (builder.ToString(), null);
        }

        /// <summary>
        /// 生成新增留言T-SQL
        /// </summary>
        /// <param name="content">留言内容</param>
        /// <param name="VID">游客ID</param>
        public static (string sql, SqlParameter[] param) Message_Insert_SQL(string content, int VID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" INSERT INTO [u_Message](Content,CreateTime,VID)");
            builder.Append(" VALUES(@Content,@CreateTime,@VID)");
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@Content",content),
                new SqlParameter("@CreateTime",DateTime.Now),
                new SqlParameter("@VID",VID)
            };
            return (builder.ToString(), parameters);
        }
        #endregion

        #region IP管理
        /// <summary>
        /// 生成查询IP单个数据T-SQL
        /// </summary>
        /// <param name="ip">ip地址</param>
        /// <returns></returns>
        public static (string sql, SqlParameter[] param) IP_Single_SQL(string ip)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" SELECT [ID],[IP],[Country],[Region],[City],[County],[Isp],[CountrID],[UpdateTime]");
            builder.Append(" FROM sys_IP");
            builder.Append(" WHERE IP = @IP");
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@IP",ip)
            };
            return (builder.ToString(), parameters);
        }

        /// <summary>
        /// 生成新增IP数据（不包含基础信息）T-SQL
        /// </summary>
        /// <param name="dic">key/value</param>
        /// <param name="ip">ip地址</param>
        public static (string sql, SqlParameter[] param) IP_Insert_SQL(string ip)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" INSERT INTO sys_IP(IP,UpdateTime)");
            builder.Append(" VALUES(@IP,@UpdateTime)");
            SqlParameter[] parameters = new SqlParameter[]
             {
                  new SqlParameter("@IP",ip),
                  new SqlParameter("@UpdateTime",DateTime.Now)
             };
            return (builder.ToString(), parameters);
        }

        /// <summary>
        /// 生成修改IP访问时间数据T-SQL
        /// </summary>
        public static (string sql, SqlParameter[] param) IP_Update_Time_SQL(string ip)
        {
            string sql = "UPDATE sys_IP SET UpdateTime = @UpdateTime WHERE IP = @IP";
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@UpdateTime",DateTime.Now),
                new SqlParameter("@IP",ip)
            };
            return (sql, parameters);
        }

        /// <summary>
        /// 生成修改IP基础数据T-SQL
        /// </summary>
        /// <param name="dic">三方接口调取的数据</param>
        public static (string sql, SqlParameter[] param) IP_Update_Info_SQL(Dictionary<string, object> dic, int ip_id)
        {
            bool isNull = dic != null;
            StringBuilder builder = new StringBuilder();
            builder.Append("  UPDATE sys_IP SET [Country]=@Country,[Region]=@Region,[City]=@City,");
            builder.Append("  [County]=@County,[Isp]=@Isp,[CountrID]=@CountrID");
            builder.Append("  WHERE [ID] = @ID");
            SqlParameter[] parameters = new SqlParameter[]
             {
                  new SqlParameter("@ID",ip_id),
                  new SqlParameter("@Country",isNull?dic["country"]:""),
                  new SqlParameter("@Region",isNull?dic["region"]:""),
                  new SqlParameter("@City",isNull?dic["city"]:""),
                  new SqlParameter("@County",isNull?dic["county"]:""),
                  new SqlParameter("@Isp",isNull?dic["isp"]:""),
                  new SqlParameter("@CountrID",isNull?dic["country_id"]:"")
             };
            return (builder.ToString(), parameters);
        }
        #endregion

        #region 游客管理
        /// <summary>
        /// 生成查询游客单个数据T-SQL
        /// </summary>
        /// <param name="IPID">IPID</param>
        public static (string sql, SqlParameter[] param) Visitor_Single_SQL(int IPID)
        {
            string sql = "SELECT [ID],[Name],[IPID],[HeadImg] FROM u_Visitor WHERE IPID=@IPID";
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@IPID",IPID)
            };
            return (sql, parameters);
        }
        /// <summary>
        /// 生成新增游客数据T-SQL
        /// </summary>
        /// <param name="IPID">IPID</param>
        public static (string sql, SqlParameter[] param) Visitor_Insert_SQL(int IPID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" INSERT INTO dbo.u_Visitor(Name,IPID,HeadImg)");
            builder.Append(" VALUES(@Name,@IPID,@HeadImg)");
            //生成1-5的随机数，目前就这几个随机头像
            Random random = new Random();
            int index = random.Next(1, 5);
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@Name",$"游客{IPID}"),
                new SqlParameter("@IPID",IPID),
                new SqlParameter("@HeadImg",$"{index}.jpg")
            };
            return (builder.ToString(), parameters);
        }
        #endregion
    }
}